package lab03.test12;

import java.sql.*;

public class Test {
    public static void main(String[] args) {

        Connection connection = null;
        Statement statement = null;
        ResultSet resultSet = null;

        //SQL语句
        String sql1 = "select * from t_users";//查询所有记录
        String sql2 = "select * from t_users where username = 'admin'";//查询用户名为'admin'的记录
        String sql3 = "select * from t_users where username = 'admin'and password = '123456'";//查询用户名为'admin'，密码为'123456'的记录
        String sql4 = "select * from t_users where register_datetime>10000000";//查询注册时间大于10000000的记录


        try {
            //加载驱动程序
            Class.forName("com.mysql.cj.jdbc.Driver");

            connection = DriverManager.getConnection(
                    "jdbc:mysql://localhost:3306/java_2017?serverTimezone=UTC",//数据库链接字符串
                    "root",
                    "123456"
            );

            statement = connection.createStatement();

            /*ResultSet:"查"才会有结果集*/

            System.out.println("查询所有记录:");
            resultSet = statement.executeQuery(sql1);
            result(resultSet);


            System.out.println("查询用户名为'admin'的记录");
            resultSet = statement.executeQuery(sql2);
            result(resultSet);

            System.out.println("查询用户名为'admin'，密码为'123456'的记录");
            resultSet = statement.executeQuery(sql3);
            result(resultSet);

            System.out.println("查询注册时间大于10000000的记录");
            resultSet = statement.executeQuery(sql4);
            result(resultSet);



        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                connection.close();
                statement.close();
                resultSet.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }

        }


    }
     static void result(ResultSet resultSet){//遍历结果集

        try {
            System.out.println("id\t\tusername\t\tpassword\t\tregister_datetime");
            while (resultSet.next()) {

                int id = resultSet.getInt(1);
                String username = resultSet.getString(2);
                String password = resultSet.getString(3);
                Long register_datetime = resultSet.getLong(4);
                System.out.println(id+"\t\t"+username+"\t\t"+password+"\t\t"+register_datetime);

            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}
